package cn.kepu.elearning.mapper;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * @author Endy
 * @create 2019-12-06 下午3:50
 **/
@Repository
public interface AllMapper {

    @Select("SELECT c.Course_id,\n" +
            "       c.Course_no,\n" +
            "       c.Course_name,\n" +
            "       c.keyWords,\n" +
            "       c.Content,\n" +
            "       c.pubStatus,\n" +
            "       c.isFirstLevelDirectory,\n" +
            "       c.Category,\n" +
            "       c.isAppShow,\n" +
            "       c.creator,\n" +
            "       c.selectedTimes,\n" +
            "       c.teacherId,\n" +
            "       (\n" +
            "           SELECT tb.teacherName\n" +
            "           FROM tchr_baseinfo tb\n" +
            "           WHERE tb.id = c.teacherId\n" +
            "       )       AS teacherName,\n" +
            "       c.orgId,\n" +
            "       eto.orgName,\n" +
            "       c.Classfication,\n" +
            "       dd.Name AS ClassficationName,\n" +
            "       c.openOrgSEQ,\n" +
            "       c.PictureURL,\n" +
            "       eto.tenantId,\n" +
            "       ct.CourseType_ID,\n" +
            "       ct.courseTypeName,\n" +
            "       ct.FullPath\n" +
            "FROM course c,\n" +
            "     resource_coursetype rc,\n" +
            "     coursetype ct,\n" +
            "     (select d.code, d.Name from ddictionary d where parentCode = 4000) dd,\n" +
            "     eosorg_t_organization eto\n" +
            "where rc.resourceId = c.Course_id\n" +
            "  and rc.courseTypeId = ct.CourseType_ID\n" +
            "  and eto.orgID = c.orgId\n" +
            "  and c.Classfication = dd.code\n" +
            "limit #{offset}, #{size}")
    List<Map<String, Object>> selectCourseByLimit(@Param("offset") int offset, @Param("size") int size);

    @Select("select t.newsId,\n" +
            "       t.newsTitle,\n" +
            "       t.newsType,\n" +
            "       t.newsContent,\n" +
            "       t.createTime,\n" +
            "       t.recommendTag,\n" +
            "       t.status,\n" +
            "       t.orgName,\n" +
            "       t.tenantId\n" +
            "from mt_trainnews t\n" +
            "limit #{offset}, #{size}")
    List<Map<String, Object>> selectNewsByLimit(@Param("offset") int offset, @Param("size") int size);

    @Select("select m.id,\n" +
            "       m.title,\n" +
            "       m.content,\n" +
            "       m.validDate,\n" +
            "       m.tenantId,\n" +
            "       t.tenantName,\n" +
            "       m.isPublic,\n" +
            "       m.hasSend,\n" +
            "       m.STATUS,\n" +
            "       m.recommendTag\n" +
            "from msg_messageinfo m,\n" +
            "     tenant t\n" +
            "where m.tenantId = t.tenantId\n" +
            "limit #{offset}, #{size}")
    List<Map<String, Object>> selectNoticeByLimit(@Param("offset") int offset, @Param("size") int size);

    @Select("SELECT\n" +
            "    t.id,\n" +
            "    t.teacherName,\n" +
            "    t.post,\n" +
            "    t.hireStyle,\n" +
            "    t.workPlace,\n" +
            "    t.expertAreaId,\n" +
            "    c.courseTypeName AS expertAreaName,\n" +
            "    t.remark,\n" +
            "    t.openOrgSEQ,\n" +
            "    t.keyWords,\n" +
            "    t.headPic,\n" +
            "    t.tenantId,\n" +
            "    t.openScope,\n" +
            "    t.orgId\n" +
            "FROM\n" +
            "    tchr_baseinfo t,\n" +
            "    coursetype c\n" +
            "WHERE\n" +
            "        t.expertAreaId = c.CourseType_ID\n" +
            "  and t.openScope !=2201\n" +
            "limit #{offset}, #{size}")
    List<Map<String, Object>> selectTeacherByLimit(@Param("offset") int offset, @Param("size") int size);

    @Select("SELECT t.id,\n" +
            "       t.trainName,\n" +
            "       t.orgId,\n" +
            "       t.attendantCount,\n" +
            "       t.classHour,\n" +
            "       t.isEnrolled,\n" +
            "       t.programStartTime,\n" +
            "       t.programEndTime,\n" +
            "       t.cad_report,\n" +
            "       eto.orgName,\n" +
            "       eto.tenantId,\n" +
            "       t.trainingContent,\n" +
            "       t.attendants,\n" +
            "       t.trainGoal,\n" +
            "       t.location,\n" +
            "       t.`comment`,\n" +
            "       t.item_type,\n" +
            "       t.openOrgSEQ,\n" +
            "       t.trainTypeID,\n" +
            "       t.pubstatus,\n" +
            "       t.approveStatus,\n" +
            "       t.startTime,\n" +
            "       t.endTime\n" +
            "FROM train t,\n" +
            "     eosorg_t_organization eto\n" +
            "WHERE t.orgId = eto.orgID\n" +
            "limit #{offset}, #{size}")
    List<Map<String, Object>> selectTrainByLimit(@Param("offset") int offset, @Param("size") int size);

    @Select("SELECT r.id,\n" +
            "       r.title,\n" +
            "       r.sponsorInfo,\n" +
            "       r.isPublish,\n" +
            "       r.detail,\n" +
            "       r.url,\n" +
            "       r.picUrl,\n" +
            "       r.mainPicUrl,\n" +
            "       r.openOrgSEQ,\n" +
            "       r.tenantId,\n" +
            "       r.publishTime,\n" +
            "       r.orgId,\n" +
            "       r.openScope,\n" +
            "       group_concat(\n" +
            "               (case\n" +
            "                    when i.itemType = 0 then (select c.Course_name from course c where c.Course_id = i.itemId)\n" +
            "                    when i.itemType = 1 then (select t.trainName from train t where t.ID = i.itemId)\n" +
            "                    when i.itemType = 2 then (select mt.newsTitle from mt_trainnews mt where mt.newsId = i.itemId)\n" +
            "                    when i.itemType = 3 then (select g.title from msg_messageinfo g where g.id = i.itemId)\n" +
            "                    when i.itemType = 4 then (select r1.title from recommendseries r1 where r1.id = i.itemId)\n" +
            "                   end) separator '&|&') name\n" +
            "FROM recommendseries r,\n" +
            "     seriesitem i\n" +
            "where i.seriesId = r.id\n" +
            "group by r.id,\n" +
            "         r.title,\n" +
            "         r.sponsorInfo,\n" +
            "         r.isPublish,\n" +
            "         r.detail,\n" +
            "         r.url,\n" +
            "         r.picUrl,\n" +
            "         r.mainPicUrl,\n" +
            "         r.openOrgSEQ,\n" +
            "         r.tenantId,\n" +
            "         r.publishTime,\n" +
            "         r.orgId\n" +
            "limit #{offset}, #{size}")
    List<Map<String, Object>> selectSeriesByLimit(@Param("offset") int offset, @Param("size") int size);

    @Select("select id, code, name from ddictionary where parentCode = 4000")
    List<Map<String, Object>> selectClassfication();

    @Select("select CourseType_ID, ParentID, courseTypeName, FullPath from coursetype where ParentID = 1")
    List<Map<String, Object>> selectExpertArea();

    @Select("select CourseType_ID, ParentID, courseTypeName, FullPath from coursetype where FullPath like '2.%'")
    List<Map<String, Object>> selectCourseType();

    @Select("select id, code, name from ddictionary where parentCode = 2100")
    List<Map<String, Object>> selectTrainType();

}
